from typing import Any, Dict, Optional, Union, List
from datetime import datetime

from sqlalchemy import select, and_, or_
from sqlalchemy.ext.asyncio import AsyncSession

from app.models.project import Project, Phase, Milestone, Task
from app.schemas.project import (
    ProjectCreate, ProjectUpdate,
    PhaseCreate, PhaseUpdate,
    MilestoneCreate, MilestoneUpdate,
    TaskCreate, TaskUpdate
)


# 项目相关服务
async def get_project_by_id(db: AsyncSession, *, id: int) -> Optional[Project]:
    return await db.get(Project, id)


async def get_project_by_code(db: AsyncSession, *, code: str) -> Optional[Project]:
    result = await db.execute(select(Project).where(Project.code == code))
    return result.scalars().first()


async def get_projects(
    db: AsyncSession, *, skip: int = 0, limit: int = 100, user_id: Optional[int] = None
) -> List[Project]:
    query = select(Project)
    if user_id:
        query = query.where(
            or_(
                Project.manager_id == user_id,
                Project.creator_id == user_id
            )
        )
    query = query.offset(skip).limit(limit)
    result = await db.execute(query)
    return result.scalars().all()


async def create_project(db: AsyncSession, *, obj_in: ProjectCreate, creator_id: int) -> Project:
    db_obj = Project(
        name=obj_in.name,
        code=obj_in.code,
        description=obj_in.description,
        status=obj_in.status,
        start_date=obj_in.start_date,
        planned_end_date=obj_in.planned_end_date,
        budget=obj_in.budget,
        priority=obj_in.priority,
        manager_id=obj_in.manager_id,
        creator_id=creator_id,
    )
    db.add(db_obj)
    await db.commit()
    await db.refresh(db_obj)
    return db_obj


async def update_project(
    db: AsyncSession, *, db_obj: Project, obj_in: Union[ProjectUpdate, Dict[str, Any]]
) -> Project:
    if isinstance(obj_in, dict):
        update_data = obj_in
    else:
        update_data = obj_in.dict(exclude_unset=True)
    for field in update_data:
        setattr(db_obj, field, update_data[field])
    db.add(db_obj)
    await db.commit()
    await db.refresh(db_obj)
    return db_obj


async def delete_project(db: AsyncSession, *, id: int) -> Project:
    project = await db.get(Project, id)
    await db.delete(project)
    await db.commit()
    return project


# 阶段相关服务
async def get_phase_by_id(db: AsyncSession, *, id: int) -> Optional[Phase]:
    return await db.get(Phase, id)


async def get_phases_by_project(
    db: AsyncSession, *, project_id: int, skip: int = 0, limit: int = 100
) -> List[Phase]:
    query = select(Phase).where(Phase.project_id == project_id).order_by(Phase.order)
    query = query.offset(skip).limit(limit)
    result = await db.execute(query)
    return result.scalars().all()


async def create_phase(db: AsyncSession, *, obj_in: PhaseCreate) -> Phase:
    db_obj = Phase(
        name=obj_in.name,
        description=obj_in.description,
        status=obj_in.status,
        start_date=obj_in.start_date,
        end_date=obj_in.end_date,
        order=obj_in.order,
        project_id=obj_in.project_id,
    )
    db.add(db_obj)
    await db.commit()
    await db.refresh(db_obj)
    return db_obj


async def update_phase(
    db: AsyncSession, *, db_obj: Phase, obj_in: Union[PhaseUpdate, Dict[str, Any]]
) -> Phase:
    if isinstance(obj_in, dict):
        update_data = obj_in
    else:
        update_data = obj_in.dict(exclude_unset=True)
    for field in update_data:
        setattr(db_obj, field, update_data[field])
    db.add(db_obj)
    await db.commit()
    await db.refresh(db_obj)
    return db_obj


async def delete_phase(db: AsyncSession, *, id: int) -> Phase:
    phase = await db.get(Phase, id)
    await db.delete(phase)
    await db.commit()
    return phase


# 里程碑相关服务
async def get_milestone_by_id(db: AsyncSession, *, id: int) -> Optional[Milestone]:
    return await db.get(Milestone, id)


async def get_milestones_by_project(
    db: AsyncSession, *, project_id: int, skip: int = 0, limit: int = 100
) -> List[Milestone]:
    query = select(Milestone).where(Milestone.project_id == project_id)
    query = query.offset(skip).limit(limit)
    result = await db.execute(query)
    return result.scalars().all()


async def create_milestone(db: AsyncSession, *, obj_in: MilestoneCreate) -> Milestone:
    db_obj = Milestone(
        name=obj_in.name,
        description=obj_in.description,
        due_date=obj_in.due_date,
        is_completed=obj_in.is_completed,
        project_id=obj_in.project_id,
        phase_id=obj_in.phase_id,
    )
    db.add(db_obj)
    await db.commit()
    await db.refresh(db_obj)
    return db_obj


async def update_milestone(
    db: AsyncSession, *, db_obj: Milestone, obj_in: Union[MilestoneUpdate, Dict[str, Any]]
) -> Milestone:
    if isinstance(obj_in, dict):
        update_data = obj_in
    else:
        update_data = obj_in.dict(exclude_unset=True)
    for field in update_data:
        setattr(db_obj, field, update_data[field])
    db.add(db_obj)
    await db.commit()
    await db.refresh(db_obj)
    return db_obj


async def delete_milestone(db: AsyncSession, *, id: int) -> Milestone:
    milestone = await db.get(Milestone, id)
    await db.delete(milestone)
    await db.commit()
    return milestone


# 任务相关服务
async def get_task_by_id(db: AsyncSession, *, id: int) -> Optional[Task]:
    return await db.get(Task, id)


async def get_tasks(
    db: AsyncSession, *, skip: int = 0, limit: int = 100,
    project_id: Optional[int] = None, phase_id: Optional[int] = None,
    assignee_id: Optional[int] = None, status: Optional[str] = None
) -> List[Task]:
    query = select(Task)
    if project_id:
        query = query.where(Task.project_id == project_id)
    if phase_id:
        query = query.where(Task.phase_id == phase_id)
    if assignee_id:
        query = query.where(Task.assignee_id == assignee_id)
    if status:
        query = query.where(Task.status == status)
    query = query.offset(skip).limit(limit)
    result = await db.execute(query)
    return result.scalars().all()


async def create_task(db: AsyncSession, *, obj_in: TaskCreate, creator_id: int) -> Task:
    db_obj = Task(
        name=obj_in.name,
        description=obj_in.description,
        status=obj_in.status,
        priority=obj_in.priority,
        start_date=obj_in.start_date,
        due_date=obj_in.due_date,
        estimated_hours=obj_in.estimated_hours,
        project_id=obj_in.project_id,
        phase_id=obj_in.phase_id,
        milestone_id=obj_in.milestone_id,
        parent_id=obj_in.parent_id,
        assignee_id=obj_in.assignee_id,
        creator_id=creator_id,
    )
    db.add(db_obj)
    await db.commit()
    await db.refresh(db_obj)
    return db_obj


async def update_task(
    db: AsyncSession, *, db_obj: Task, obj_in: Union[TaskUpdate, Dict[str, Any]]
) -> Task:
    if isinstance(obj_in, dict):
        update_data = obj_in
    else:
        update_data = obj_in.dict(exclude_unset=True)
    
    # 如果任务状态变为已完成，自动设置完成日期
    if update_data.get("status") == "completed" and not db_obj.completed_date:
        update_data["completed_date"] = datetime.utcnow()
        update_data["progress"] = 100.0
    
    for field in update_data:
        setattr(db_obj, field, update_data[field])
    db.add(db_obj)
    await db.commit()
    await db.refresh(db_obj)
    return db_obj


async def delete_task(db: AsyncSession, *, id: int) -> Task:
    task = await db.get(Task, id)
    await db.delete(task)
    await db.commit()
    return task